﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Configuration.Provider;
using Support.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    public abstract class BaseSQLDAL
    {
        protected string GetConnectionString_Sindicato()
        {
            try
            {
                return Support.Configuration.ConfigurationManager.ConnectionString_Sindicato;
            }
            catch (ProviderException ex)
            {
                throw new ProviderException("Error al recuperar el string de conexión a la BD. Verifique que la conexión exista en el archivo de configuración.", ex);
            }
            catch (Exception ex)
            {
                throw new ProviderException("Error al recuperar el string de conexión a la BD.", ex);
            }
        }

        protected void AddCmdParameter(string name, object value, ParameterDirection parameterDirection)
        {
            SqlParameter param = new SqlParameter(name, value);
            param.Direction = parameterDirection;
            cmd.Parameters.Add(param);
        }

        protected void AddCmdParameter(string name, object value, ParameterDirection parameterDirection, DbType dbType)
        {
            SqlParameter param = new SqlParameter(name, value);
            param.Direction = parameterDirection;
            param.DbType = dbType;
            cmd.Parameters.Add(param);
        }

        public SqlConnection connection = null;
		//objeto para la ejecucion de sentencias
        private SqlCommand cmd = null;
		//objeto para la lectura de registros
		private SqlDataReader dr = null;
		//objeto para realizar la transaccion
		private SqlTransaction tr = null;
        //datatable para retornar informacion
        protected DataTable dt = null;

		private bool _Transaction = false;
		public bool Transaction
		{
			get { return _Transaction; }
			set { _Transaction = value; }
		}

        /// <summary>
        /// Propiedad para establecer una conexion en comun para transaccionalidad
        /// </summary>
        public SqlConnection GenericConexion
        {
            get { return connection; }
            set { connection = value; }
        }

        /// <summary>
        /// Propiedad para establecer el objeto para la transaccion
        /// </summary>
        public SqlTransaction GenericTransaccion
        {
            get { return tr; }
            set { tr = value; }
        }

        public BaseSQLDAL()
		{
            connection = new SqlConnection(Support.Configuration.ConfigurationManager.ConnectionString_Sindicato);
		}

        protected void CreateCommand(string sql, bool use_store_proc)
        {
            cmd = new SqlCommand(sql, connection);
            cmd.CommandTimeout = connection.ConnectionTimeout;
            if (_Transaction)
            {
                OpenConnection();
                if (tr == null)
                {
                    tr = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                }
                cmd.Transaction = tr;
            }
            cmd.CommandType = CommandType.Text;
            if (use_store_proc)
                cmd.CommandType = CommandType.StoredProcedure;
        }

        /// <summary>
        /// Metodo que permite la apertura de la base de datos
        /// </summary>
        protected void OpenConnection()
        {
            if (connection.State != ConnectionState.Open)
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Metodo que permite el cierre de la conexión a la base de datos
        /// </summary>
        public void CloseConnection()
        {
            connection.Close();
            connection.Dispose();
        }

        /// <summary>
        /// Cierra el reader
        /// </summary>
        public void CloseReader()
        {
            if (dr != null)
                dr.Close();
        }

        /// <summary>
        /// Función que retorna la informacin en un DataReader
        /// </summary>
        /// <returns></returns>
        public SqlDataReader ExecuteReader()
        {
            try
            {
                OpenConnection();
                if (_Transaction)
                {
                    dr = cmd.ExecuteReader();
                }
                else
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                return dr;
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        /// <summary>
        /// Función que permite la lectura de datos de un DataReader
        /// </summary>
        /// <returns></returns>
        public bool Read()
        {
            if (dr.Read())
            {
                return true;
            }
            else
            {
                if (!_Transaction)
                {
                    CloseConnection();
                }
            }
            return false;
        }

        public SqlDataReader GetDataReader()
        {
            return dr;
        }

        protected void DisposeCommand()
        {
            cmd.Dispose();
        }

        protected void DisposeDataReader()
        {
            dr.Dispose();
        }

        protected object GetOutParameter(string NameParameter)
        {
            return cmd.Parameters[NameParameter].Value;
        }

        protected object GetOutResult(string NameParameter)
        {
            return dr[NameParameter];
        }
        
    }
}
